package com.iflytek.universitymanagement.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.iflytek.universitymanagement.domain.ScoreAnalysisResult;
import com.iflytek.universitymanagement.domain.StudentScore;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StudentScoreMapper extends BaseMapper<StudentScore> {

    // 联合查询学生成绩和课程名称
    @Select("SELECT ss.*, c.course_name FROM student_score ss " +
            "JOIN course c ON ss.course_id = c.course_id " +
            "WHERE ss.student_id = #{studentId}")
    List<StudentScore> getStudentScoresWithCourseName(Integer studentId);

    // 分页查询所有学生成绩并连接课程表获取课程名称
    @Select("SELECT ss.*, c.course_name FROM student_score ss " +
            "JOIN course c ON ss.course_id = c.course_id")
    Page<StudentScore> getAllStudentScores(Page<StudentScore> page);

    // 根据学生 ID 获取学生姓名
    @Select("SELECT name FROM student WHERE student_id = #{studentId}")
    String getStudentNameById(Integer studentId);


    @Select("SELECT sc.*, s.name AS name, c.course_name " +
            "FROM student_score sc " +
            "LEFT JOIN student s ON sc.student_id = s.student_id " +
            "LEFT JOIN course c ON sc.course_id = c.course_id ${ew.customSqlSegment}")
    IPage<StudentScore> selectPage(IPage<StudentScore> page, @Param("ew") QueryWrapper<StudentScore> queryWrapper);


    @Select("SELECT * FROM student_score WHERE student_id = #{studentId} ORDER BY semester DESC LIMIT 5")
    List<StudentScore> getRecentScores(Integer studentId);



//    @Select("SELECT s.name AS studentName,  c.course_name, ss.score, ss.progress, ss.semester " +
//            "FROM student_score ss " +
//            "JOIN student s ON ss.student_id = s.student_id " +
//            "JOIN course c ON ss.course_id = c.course_id")
//    IPage<StudentScore> getStudentScoresPage(Page<StudentScore> page);

    // 分页查询所有学生成绩、学生姓名和课程名称
    @Select("SELECT ss.score_id, ss.course_id, ss.student_id, ss.score, ss.semester, ss.progress, s.name , c.course_name " +
            "FROM student_score ss " +
            "JOIN student s ON ss.student_id = s.student_id " +
            "JOIN course c ON ss.course_id = c.course_id")
    IPage<StudentScore> getAllScoresWithDetails(Page<?> page);
    @Select("SELECT ss.*, c.course_name " +
            "FROM student_score ss " +
            "LEFT JOIN course c ON ss.course_id = c.course_id " +
            "WHERE ss.student_id = (SELECT student_id FROM student WHERE name = #{name})")
    List<StudentScore> getStudentProgressAndScoresByName(@Param("name") String name);
}
